# -*- coding: utf-8 -*-
import os,sys
import socket
import SocketServer 
import psycopg2
from optparse import OptionParser
import logging
import globalvar
import traceback
import datetime
import subprocess

class UCHandler(SocketServer.BaseRequestHandler):
    def finish(self):
        globalvar.socketlogging.debug("Disconnect :")
        globalvar.socketlogging.debug(self.client_address)

    def setup(self):
        globalvar.socketlogging.info("There is a connection:")
        globalvar.socketlogging.info(self.client_address)
        
    def parseprotocol(self):

        self.cur_pos = 0
        self.commandid=self.protocal_data[self.cur_pos:self.cur_pos + globalvar.COMMANDID_LENGTH]
        self.cur_pos = self.cur_pos + globalvar.COMMANDID_LENGTH
        globalvar.socketlogging.debug('commandid =%s'%(self.commandid))

        self.splitstr=self.protocal_data[self.cur_pos:self.cur_pos + 1]
        self.cur_pos = self.cur_pos + 1
                
        self.commandtype=self.protocal_data[self.cur_pos:self.cur_pos + globalvar.COMMANDTYPE_LENGTH]
        self.cur_pos = self.cur_pos + globalvar.COMMANDTYPE_LENGTH
        globalvar.socketlogging.debug('commandtype =%s'%(self.commandtype))

        self.splitstr=self.protocal_data[self.cur_pos:self.cur_pos + 1]
        self.cur_pos = self.cur_pos + 1

        self.deviceid=self.protocal_data[self.cur_pos:self.cur_pos + globalvar.DEVICEID_LENGTH]
        self.cur_pos = self.cur_pos + globalvar.DEVICEID_LENGTH
        globalvar.socketlogging.debug("sefl.deviceid is %s"%(self.deviceid))

        self.splitstr=self.protocal_data[self.cur_pos:self.cur_pos + 1]
        self.cur_pos = self.cur_pos + 1

        self.datetimestr=self.protocal_data[self.cur_pos:self.cur_pos + globalvar.DATETIME_LENGTH]
        globalvar.socketlogging.debug("self.datetimetstr is %s"%(self.datetimestr))
        self.cur_pos = self.cur_pos + globalvar.DATETIME_LENGTH
        self.splitstr=self.protocal_data[self.cur_pos:self.cur_pos + 1]
        self.cur_pos = self.cur_pos + 1


    def getsplitchar(self):
        #get the split chr
        globalvar.socketlogging.debug("get the split str ")
        self.splitstr=self.request.recv(2)
        
    def handle(self):

        while 1:
            try:
                self.commandid = '0000'
                header_str = self.request.recv(16)
                if(len(header_str)<16):
                    globalvar.socketlogging.error("receive header len str <16,recv len = %d ,and the content is %s"%(len(header_str),header_str))
                    break
                
                header_str=header_str.decode('utf-16-le')
            
                globalvar.socketlogging.debug("header str  is %s:"%(header_str))
            
                self.getsplitchar()
            
                self.bodylen = int(header_str,16)
            
                chunk=self.request.recv(self.bodylen)
                while len(chunk) < self.bodylen:
                    chunk=chunk+self.request.recv(self.bodylen-len(chunk))
            
                self.protocal_data=chunk
            
                #decode the unicode
                self.protocal_data = self.protocal_data.decode('utf-16-le')
                self.parseprotocol()
                globalvar.socketlogging.debug("recv %s command"%(self.commandid))
                if(self.commandid == globalvar.COMMAND_REGISTER):
                    self.HandleRegister()
                elif (self.commandid == globalvar.COMMAND_QUERYTASK):
                    self.HandleQueryTask()
                elif (self.commandid == globalvar.COMMAND_UPDATETASK):
                    self.HandleUpdateTask()
                elif(self.commandid == globalvar.COMMAND_REQUESTTASK):
                    self.HandleRequestTask()               
                elif(self.commandid == globalvar.COMMAND_COMMITTASK):
                    self.HandleCommitTask()
                elif(self.commandid == globalvar.COMMAND_COMMIT_FINISH_TASK):
                    self.HandleCommitFinishTask()                    
                    
                globalvar.socketlogging.debug("finish a loop")    
            
            except socket.timeout:
                globalvar.socketlogging.error("time out error")
                break
            except socket.error:
                globalvar.socketlogging.error("socke error")
                #self.SendStatus(globalvar.STATUS_ERROR_SOCKET)
                break
            except:
                exption_str=traceback.format_exc()
                globalvar.socketlogging.error( "Unexpected error:\n %s ",exption_str )
                self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
                break
            
    def SendStatus(self,status):
        content_str=self.commandid + globalvar.SPLITCHAR + '0200' + globalvar.SPLITCHAR +  self.GetCurrentTime() + globalvar.SPLITCHAR  + status + globalvar.SPLITCHAR +globalvar.ENDCHAR
        self.SendContent(content_str)
        
    def GetIntValueFromStr(self,str):
        if(len(str) == 0):
            return 0
        if(len(str) > 8):
            return int(str[0:8],16)
        else:
            return int(str,16)

        
    def NewTaskRecord(self,table_name,records_list):
        
        globalvar.cursor.execute("select column_name,data_type from information_schema.columns where table_name = \'%s\'order by ordinal_position"%(table_name))
        type_records = globalvar.cursor.fetchall()
        column_format = ''
        values_format = ''
        values_index = 0
        
        for item in type_records:
            if values_index  == 0:
                column_format = "\"" + item[0] + "\""
                if(item[1] == "timestamp without time zone"):
                    values_format = "to_timestamp(\'%s\',\'YYYY-MM-DD HH24:MI:SS\')"
                elif (item[1] == 'integer'):
                    
                    if(records_list[values_index] == 'nextval(\'task_id_seq\'::regclass)'):
                        values_format = "%s" 
                    else:
                        values_format ="\'" + "%s" + "\'"
                        records_list[values_index] =  self.GetIntValueFromStr(records_list[values_index])       
                else:    
                        values_format ="\'" + "%s" + "\'"
            else:
                column_format = column_format + "," + "\"" + item[0] + "\""                            
                
               #---------not empty------------------------------------
                if(item[1] == "timestamp without time zone"):
                    values_format =  values_format + "," + "to_timestamp(\'%s\',\'YYYY-MM-DD HH24:MI:SS\')"
                    time_str = self.GetDateTimeFromYYYYMMDDHHMMSS(records_list[values_index])
                    records_list[values_index] = time_str.strftime("%Y-%m-%d %H:%M:%S")
                elif(item[1] == 'integer'):   
                    if(records_list[values_index] == 'nextval(\'task_id_seq\'::regclass)'):
                        values_format = values_format + "," + "%s"
                    else:	  
                        values_format = values_format + "," + "\'" + "%s" + "\'"
                        records_list[values_index] =  self.GetIntValueFromStr(records_list[values_index])	
                else:
                    values_format = values_format + "," + "\'" +"%s" + "\'"
            values_index = values_index + 1

#end of the for statement
        sql_sub_str = "insert into %s (%s)  values (%s);" %(table_name,column_format,values_format)
        sql_str = sql_sub_str%(tuple(records_list))
        globalvar.cursor.execute(sql_str)
        globalvar.conn.commit()
        
        self.SendStatus(globalvar.STATUS_OK)                
                
    def HandleCommitTask(self):
        all_values = self.protocal_data[self.cur_pos:-2]
        all_values_list=all_values.split('\t')
        globalvar.databaselogging.debug("the length is %d"%(len(all_values_list)))        
        all_values_list[0] = u'nextval(\'task_id_seq\'::regclass)'
        for i in range(len(all_values_list)):
            all_values_list[i] = all_values_list[i].encode('utf-8')
        globalvar.databaselogging.debug(all_values_list)                        
        #set the state to 2
        all_values_list[12]='2'
        self.NewTaskRecord('task',all_values_list)
        
    def HandleCommitFinishTask(self):
        
        all_values = self.protocal_data[self.cur_pos:-2]
        all_values_list=all_values.split('\t')
        globalvar.databaselogging.debug("the length is %d"%(len(all_values_list)))        
        all_values_list[0] = u'nextval(\'task_id_seq\'::regclass)'
        for i in range(len(all_values_list)):
            all_values_list[i] = all_values_list[i].encode('utf-8')
        globalvar.databaselogging.debug(all_values_list)                        
        #set the state to 6
        all_values_list[12]='6'
        self.NewTaskRecord('task',all_values_list)
        
    def HandleUpdateTask(self):
        
        #first remove the old record
    
        all_values = self.protocal_data[self.cur_pos:]
        all_values_list=all_values.split('\t')
        for i in range(len(all_values_list)):
            all_values_list[i] = all_values_list[i].encode('utf-8')
        globalvar.databaselogging.debug(all_values_list)
#	all_values[0]=int(all_values[0],16)
        all_values_list[12]='2'
        globalvar.cursor.execute("delete from task where id=%s"%(int(all_values_list[0],16)))
        globalvar.databaselogging.debug("delete from task where id=%s"%(int(all_values_list[0],16)))
        globalvar.conn.commit()
        globalvar.socketlogging.debug("the record_id %s changed from state 1 to 2( 从收单派收到 )"%(all_values_list[0]))                
        self.NewTaskRecord('task',all_values_list)
        

    def HandleQueryTask(self):
        #first check the state 0 
        globalvar.cursor.execute("select count(*) from task where \"posNo\" = %s and state = %s;",(self.deviceid,0,))
        records = globalvar.cursor.fetchall()
        numtask = records[0]
        globalvar.databaselogging.debug( "the device %s have %d task"%(self.deviceid,numtask[0])) 
        content_str ='1002' + globalvar.SPLITCHAR + '0200' +  globalvar.SPLITCHAR + self.GetCurrentTime() + globalvar.SPLITCHAR + "%08x"%(numtask) + globalvar.SPLITCHAR + globalvar.ENDCHAR
        self.SendContent(content_str)
        
    def SendContent(self,content):
        str_utf8 = content.decode('utf-8')
        globalvar.socketlogging.debug("send str :%s"%(str_utf8))
        str_utf16 = str_utf8.encode('utf-16-le')
        length_str = '%08x'%(len(str_utf16))
        sendstr = length_str.encode('utf-16-le') + globalvar.SPLITCHAR.encode('utf-16-le') + str_utf16
        self.request.sendall(sendstr)
        
    def GetNowDatabaseFormat(self):
        n=datetime.datetime.now()
        return n.strftime("%Y-%m-%d %H:%M:%S")

    def HandleRegister(self):
        globalvar.databaselogging.debug("the now format is : %s"%(self.GetNowDatabaseFormat()))
        str_now = self.GetNowDatabaseFormat()
        globalvar.cursor.execute(" update freeterminal set \"isOnline\"=1,\"lastLoginTime\"=to_timestamp(%s,'YYYY-MM-DD HH24:MI:SS') where \"posNo\" = %s;",(str_now,self.deviceid,))
        self.SendStatus(globalvar.STATUS_OK)
        #after send we commit                 
        globalvar.conn.commit()
        
    def GetCurrentTime(self):
        n=datetime.datetime.now()
        return n.strftime('%Y%m%d%H%M%S')
    def GetDateTimeFromYYYYMMDDHHMMSS(self,str):
        dt = datetime.datetime(int(str[0:4]),int(str[4:6]),int(str[6:8]),int(str[8:10]),int(str[10:12]),int(str[12:14]))
        return dt        
    def HandleRequestTask(self):

        #first check the state 0 
        globalvar.cursor.execute("select * from task where \"posNo\" = %s and (state = %s or state= %s);",(self.deviceid,0,7))
        records = globalvar.cursor.fetchall()
        globalvar.databaselogging.debug( "the device %s have %d task"%(self.deviceid,len(records))) 
        content_header ='1004' + globalvar.SPLITCHAR + '0200' + globalvar.SPLITCHAR + self.GetCurrentTime() + globalvar.SPLITCHAR
        for eachitem in records:
            item_str=''
            for subitem in eachitem:
                subitem_trip=''
                if(type(subitem) == type('')):
                    subitem_trip=subitem.strip()
                if(type(subitem) == type(0)):
                    subitem_trip="%x"%(subitem)
                if item_str == '':
                    item_str = subitem_trip
                else:    
                    item_str = item_str + globalvar.TABCHAR + subitem_trip 
            record_id=int(eachitem[0])	
            globalvar.socketlogging.debug("the record_id = %d"%(record_id))		
            content_str = content_header + item_str + globalvar.SPLITCHAR +  globalvar.ENDCHAR
            self.SendContent(content_str)
            globalvar.cursor.execute("update task set state=1 where id= %s;",(record_id,))
            globalvar.conn.commit()
            globalvar.socketlogging.debug("the record_id %s changed from state 0 to 1( 从客户提请到收单派收 )"%(record_id))
"""
            Delete the send sms action
            sms_number = eachitem[2]
            #find the company name
            globalvar.socketlogging.debug("the id is %s"%(eachitem[-12]))
            company_id = int(eachitem[-12])
            globalvar.cursor.execute("select name from company where id= %s;",(company_id,))
            company_records = globalvar.cursor.fetchall()
            company_name = company_records[0][0]
            globalvar.socketlogging.debug("the comanpy is %s"%(company_name))
            sms_text = globalvar.SMS1_TEMPLATE%(eachitem[1],company_name,eachitem[16],eachitem[-2])
            globalvar.socketlogging.debug("send the sms is %s"%(sms_text))
            ipPipe = subprocess.Popen(('../unittest/sdk4/SendSMS',sms_number,sms_text), stdout=subprocess.PIPE)
            ipPipe.wait() 
            ipStr = ipPipe.stdout.read()
            globalvar.socketlogging.debug(ipStr)
            globalvar.socketlogging.debug("return code is %d"%(ipPipe.returncode)) 
"""
def main(*argv):

    if argv:
        argv = list(argv)
    else:
        argv = sys.argv[1:]
    usage='python ems_server.py -f [filename]'
    parser = OptionParser(usage=usage)    
    parser.add_option("-f", "--filename", dest="logfile",type="string",help="indicate the output filelog" ,default="test.log")
    parser.add_option("-d", "--debug", dest="debug",type="int",help="indicate the output filelog",default=0)

    (options, args) = parser.parse_args(args=argv)

    if  options.logfile:
        logfile = os.path.abspath(options.logfile)    

        logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(name)-12s  %(levelname)-8s %(message)s',
                    filename=logfile,
                    datefmt="%m-%d %H:%M:%S",        
                    filemode='a')

    console=logging.StreamHandler()
    console.setLevel(logging.DEBUG)
    formatter = logging.Formatter('%(name)-12s: %(levelname)-8s %(message)s')
    console.setFormatter(formatter)
    logging.getLogger('').addHandler(console)

    globalvar.socketlogging = logging.getLogger('Socket')
    globalvar.databaselogging = logging.getLogger('DataBase')

#set up the database
    try:
        if(options.debug):
            globalvar.conn = psycopg2.connect(database='test',host='localhost',user='postgres',password='postgres')
        else:
            globalvar.conn = psycopg2.connect(database='logistics',host='localhost',user='postgres',password='postgres')        
        globalvar.cursor = globalvar.conn.cursor()
    except psycopg2.DatabaseError:
        globalvar.databaselogging.error("Database setup error")
        return -1
    
    logging.debug("start server")

    if(options.debug == 0):
        myaddr = ('', 2345)
	print "the not debug version"
    else:
        myaddr = ('', 5432)    
	print "the debug version"
    myserver = SocketServer.ThreadingTCPServer(myaddr,UCHandler)
    myserver.serve_forever()

if __name__ == '__main__':
    main()
